﻿using System;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Text;
using Utils;

public partial class sql_window : CustomBasePage
{

  #region Properties
  private DropDownList[] holderForDdl; 
  public DropDownList[] HolderForDdl
  {
    get
    {
      if (holderForDdl == null)
      {
        holderForDdl = new DropDownList[10];
      } //eof if holder is null 
      return holderForDdl;
    } //EOF get
    set { holderForDdl = value; }
  } //comm -- eof holderForDdl property 
  #endregion Properties

 
  #region TemplateMethods

  protected override void OnInit(EventArgs e)
  {
    Utils.Debugger.WriteIf("OnInit START sql_window");
    base.OnInit(e);

    //CHECK THAT THE USER HAS LOGGED IN
    if (
      Session["global.LOGGEDINOK"] == null || 
      !Convert.ToString(Session["global.LOGGEDINOK"]).Equals("OK")      )
    {
      Response.Redirect("~/admin/login.aspx", false);
    }


  } //eof OnInit


  protected override void CreateChildControls()
  {
    base.CreateChildControls();
    CreateDynamicControls();
  }


  protected override object SaveViewState()
  {
    return new Pair(base.SaveViewState(), null);
  }

  protected override void LoadViewState(object savedState)
  {
    base.LoadViewState(((Pair)savedState).First);
    EnsureChildControls();
  }


  protected void Page_Load(object sender, EventArgs e)
  {
    //Utils.Debugger.WriteIf ( "Item.aspx.cs Page_Load  start " );


  } //eof Page_Load
  ////////////////////////////////////////////////////////////////////////////////////////////////////////// TEMPLATE METHODS END
  #endregion //TemplateMethods



  #region CreateDynamicControls
  
  private void CreateDynamicControls()
  {
    this.Master.ErrorLabel.Text = (string)Session["global.msg"];
    txtInformer.Text = String.Empty;

    Utils.Debugger.WriteIf("sql_window -- CreateDynamic Controls START");

    panGuiders.Controls.Clear();

    this.RecreateDynControlsAfterPostBack();
  } //eof method CreateDynamicControls
  
  #endregion //CreateDynamicControls



  #region DisplayMethods
  private void DisplayProcText(ref  string echo)
  {
    Utils.Debugger.WriteIf("START -- sql_window --- DisplayProcText ");
    Session[base.PageName + ".flagClickGenerateUpdateProc"] = 1;
    DataSet ds = new DataSet();
    string msg = String.Empty;
    string procToShow = (string)Session[base.PageName + ".procName"];
    //debug Debugger.WriteIf("CHECK DisplayProcText --- the procToShow is " + procToShow);
    string connectionStr = (string)Session["global.ConnectionStr"];

    
    nsDb.MetaGetter mg = new nsDb.MetaGetter ( connectionStr ) ;

    

    if ( mg.GetProcMetaText ( ref msg , DomainName , procToShow , ref ds ))
    {
      this.Master.ErrorLabel.ForeColor = System.Drawing.Color.Green ;
      Utils.Debugger.DebugDataSet("CHECK --- GetProcMetaText , ref ds ", ref ds);
      Utils.Data.DataSetGastArbeiter.DsToString(ref echo, ref ds);
      txtInformer.Text = echo;
    }
    else 
    {
      this.Master.ErrorLabel.ForeColor = System.Drawing.Color.Red ; 
    }


    this.Master.ErrorLabel.Text = msg;
  } //eof method 


  private void DisplayDropDataBaseSelector(ref DropDownList[] holderForDdl)
  {

    DropDownList dropDataBaseSelector = holderForDdl[0];
    if (dropDataBaseSelector == null)
    {
      dropDataBaseSelector = new DropDownList();
      dropDataBaseSelector.ID = "dropDataBaseSelector";
    }


    string msg = String.Empty;
    DataSet dsForDataBases = base.PopulateListOfDbObjects(ref msg, "sys.databases");
    string DataBaseName = (string)Session["global.CurrentDb"];

    if (dsForDataBases != null && dsForDataBases.Tables[0] != null)
    {
      //Utils.Debugger.DebugDataSet("displayDropDataBaseSelector , sql_window", ref dsForDataBases);
      DataTable dt = new DataTable();
      dt = dsForDataBases.Tables[0];
      DataRow dr = dt.NewRow();
      dr[dt.Columns[0].ColumnName.ToString()] = "SELECT DataBase";
      dt.Rows.InsertAt(dr, 0);   //add at the first row
      dropDataBaseSelector.DataSource = dt;
      dropDataBaseSelector.DataTextField = dt.Columns[0].ColumnName.ToString();

      for (int dropRowNum = 0; dropRowNum < dsForDataBases.Tables[0].Rows.Count; dropRowNum++)
      {

        DataRow drow = dsForDataBases.Tables[0].Rows[dropRowNum];
        ListItem item = new ListItem();
        item.Value = Convert.ToString(drow[0]).Trim();
        item.Text = (string)drow[0];
        //debugOk Utils.Debugger.WriteIf ("drow [0 ] is  " + (string)drow[0] );
        dropDataBaseSelector.Items.Add(item);
      } //eof method for int dropRowNum 


      dropDataBaseSelector.EnableViewState = true;
      dropDataBaseSelector.Visible = true;



      //the first load of the page = the dropdown does not have selected values 
      if (DataBaseName != null && DataBaseName.Equals(String.Empty) == false)
        dropDataBaseSelector.SelectedValue = DataBaseName;

      if (DataBaseName == null)
        dropDataBaseSelector.SelectedIndex = 0;




      dropDataBaseSelector.CssClass = "cssClassSelect";
      dropDataBaseSelector.Style.Add("margin-left", "200px");
      dropDataBaseSelector.SelectedIndexChanged += ClickDropDataBaseSelector;
      dropDataBaseSelector.AutoPostBack = true;
      dropDataBaseSelector.EnableViewState = true;


      Utils.Debugger.WriteIf("CHECK -- DisplayDropDataBaseSelector sel value is : " + dropDataBaseSelector.SelectedValue);
      dropDataBaseSelector.DataBind();
      holderForDdl[0] = dropDataBaseSelector;
      panGuiders.Controls.Add(dropDataBaseSelector);

    } //eof if DataBaseName is not null 
    else
    {


    } //eof else 
    this.Master.ErrorLabel.Text = msg;

  } //eof method 


  private void DisplayDropTableSelector(ref DropDownList[] holderForDdl)
  {
    string msg = String.Empty;
    DataSet dsForTables = base.PopulateListOfDbObjects(ref msg , "sys.tables");


    DropDownList dropTableSelector = holderForDdl[1];
    if (dropTableSelector == null)
    {
      dropTableSelector = new DropDownList();
      dropTableSelector.ID = "dropTableSelector";
    }





    dropTableSelector.CssClass = "cssClassSelect";
    dropTableSelector.Style.Add("margin-left", "200px");
  


    if (dsForTables != null && dsForTables.Tables[0] != null)
    {
      //Utils.Debugger.DebugDataSet("displayDropTableSelector , sql_window", ref dsForTables);
      DataTable dt = new DataTable();
      dt = dsForTables.Tables[0];
      DataRow dr = dt.NewRow();
      dr[dt.Columns[0].ColumnName.ToString()] = "SELECT TABLE";
      //dt.Rows.Add(dr);
      dt.Rows.InsertAt(dr, 0);
      dropTableSelector.DataSource = dt;
      dropTableSelector.DataTextField = dt.Columns[0].ColumnName.ToString();

      for (int dropRowNum = 0; dropRowNum < dsForTables.Tables[0].Rows.Count; dropRowNum++)
      {

        DataRow drow = dsForTables.Tables[0].Rows[dropRowNum];
        ListItem item = new ListItem();
        item.Value = Convert.ToString(drow[0]).Trim();
        item.Text = (string)drow[0];
        //debugOk Utils.Debugger.WriteIf ("drow [0 ] is  " + (string)drow[0] );
        dropTableSelector.Items.Add(item);
      } //eof method for int dropRowNum 


      dropTableSelector.EnableViewState = true;
      dropTableSelector.Visible = true;


      string tableName = Convert.ToString(Session[base.PageName + ".tableName"]);
      Utils.Debugger.WriteIf("CHECK sql_window DisplayDropTableSelector The table name is " + tableName);
      //the first load of the page = the dropdown does not have selected values 
      if (tableName != null && tableName.Equals(String.Empty) == false)
        dropTableSelector.SelectedValue = tableName;

      if (tableName == null)
        dropTableSelector.SelectedIndex = 0;

      //Utils.Debugger.WriteIf("tHE SELECTED VALUE IS " + dropTableSelector.SelectedValue);
      
      dropTableSelector.SelectedIndexChanged += new EventHandler(ClickDropTableSelector);
      dropTableSelector.DataBind();
      tableName = tableName.Trim();

      holderForDdl[1] = dropTableSelector;
      panGuiders.Controls.Add(dropTableSelector);
      //  this.Master.ErrorLabel.ForeColor = System.Drawing.Color.Green;
      msg = " ";
      //	this.Master.ErrorLabel.Text = msg;




    } //eof if tableName is not null 
    else
    {


    } //eof else 
    this.Master.ErrorLabel.Text = msg;

  } //eof method 


  private void DisplayDropProcSelector(ref DropDownList[] holderForDdl)
  {

    DropDownList dropProcSelector = holderForDdl[2];
    if (dropProcSelector == null)
    {
      dropProcSelector = new DropDownList();
      dropProcSelector.ID = "dropProcSelector";
    }

    
    
    dropProcSelector.CssClass = "cssClassSelect";
    dropProcSelector.Style.Add(HtmlTextWriterStyle.MarginLeft, "200px");
    dropProcSelector.Style.Add(HtmlTextWriterStyle.MarginRight, "10px");




    string msg = String.Empty;
    DataSet dsForTables = base.PopulateListOfDbObjects(ref msg , "sys.procedures");

    if (dsForTables != null && dsForTables.Tables[0] != null)
    {

      DataTable dt = new DataTable();
      dt = dsForTables.Tables[0];
      DataRow dr = dt.NewRow();
      dr[dt.Columns[0].ColumnName.ToString()] = "SELECT Proc";
      dt.Rows.InsertAt(dr, 0);         //insert at first row !!!
      dropProcSelector.DataSource = dt;
      dropProcSelector.DataTextField = dt.Columns[0].ColumnName.ToString();

      for (int dropRowNum = 0; dropRowNum < dsForTables.Tables[0].Rows.Count; dropRowNum++)
      {

        DataRow drow = dsForTables.Tables[0].Rows[dropRowNum];
        ListItem item = new ListItem();
        item.Value = Convert.ToString(drow[0]).Trim();
        item.Text = (string)drow[0];
        //debugOk Utils.Debugger.WriteIf ("drow [0 ] is  " + (string)drow[0] );
        dropProcSelector.Items.Add(item);
      } //eof method for int dropRowNum 



      //Utils.Debugger.WriteIf("My Proc name is " + procName);
      //Utils.Debugger.WriteIf("tHE SELECTED VALUE IS " + dropProcSelector.SelectedValue);
      dropProcSelector.EnableViewState = true;
      dropProcSelector.Visible = true;
      dropProcSelector.AutoPostBack = true;
      dropProcSelector.SelectedIndexChanged += new EventHandler(ClickDropProcSelector);

      

      string procName = Convert.ToString(Session[base.PageName + ".procName"]).Trim();
      //debug Utils.Debugger.WriteIf("The Proc name is " + procName);
      //the first load of the page = the dropdown does not have selected values 
      if (procName != null && procName.Equals(String.Empty) == false)
        dropProcSelector.SelectedValue = procName;

      if (procName == null)
        dropProcSelector.SelectedIndex = 0;


      //  this.Master.ErrorLabel.ForeColor = System.Drawing.Color.Green;
      msg = " ";
      //	this.Master.ErrorLabel.Text = msg;
      
      
      dropProcSelector.DataBind();
      holderForDdl[2] = dropProcSelector; 
      panGuiders.Controls.Add(dropProcSelector);


    } //eof if procName is not null 
    
    {
      ; //do nothing ?!

    } //eof else 
    this.Master.ErrorLabel.Text = msg;
    panGuiders.Controls.Add(dropProcSelector);
  } //eof method 
  

  private void DisplayOutput()
  {
    DataSet dsMeta = new DataSet();
    string tableName = Convert.ToString(Session[base.PageName + ".tableName"]).Trim();
    //debug Utils.Debugger.WriteIf("CHECK sql_window DisplayOutput --- tableName is " + tableName);


    string msg = String.Empty;
    string echo = String.Empty;
    string connectionStr = (string)System.Web.HttpContext.Current.Session["global.connectionStr"];
    nsDb.MetaDataBaseControl mdbc = new nsDb.MetaDataBaseControl(connectionStr ) ;

    if (mdbc.GetTableMetaData(ref msg, ref dsMeta, ref tableName, 1))
    {
      Utils.Debugger.WriteIf("OK");
    }
    //Utils.Debugger.DebugDataSet("from sql_window", ref dsMeta);

    if (
       Session[base.PageName + ".flagClickDropProcSelector"] != null &&
       (int)Session[base.PageName + ".flagClickDropProcSelector"] == 1)
    {
      
      this.DisplayProcText(ref echo);
      Session[base.PageName + ".flagClickGenerateUpdateProc"] = null;

    } //eof else if

    else if (
       Session[base.PageName + ".flagClickGenerateInsertValuesProc"] != null &&
       (int)Session[base.PageName + ".flagClickGenerateInsertValuesProc"] == 1)
    {
      GenerateInsertValuesProcedure(ref dsMeta, ref echo);
      Session[base.PageName + ".flagClickGenerateInsertValuesProc"] = null;
    }


    //all those above require a valid table name if none has been selected do nothing
    if (tableName == null)
    {
      this.Master.ErrorLabel.Text = "Please , select a table for this action !!!";
      this.Master.ErrorLabel.ForeColor = System.Drawing.Color.Red;
      txtInformer.Text = String.Empty;
      Session[base.PageName + ".tableName"] = null;
      return;

    }

    if (
        Session[base.PageName + ".flagClickGenerateInsertProc"] != null &&
        (int)Session[base.PageName + ".flagClickGenerateInsertProc"] == 1)
    {
      GenerateInsertProcedure(ref dsMeta, ref echo);
      Session[base.PageName + ".flagClickGenerateInsertProc"] = null;
    }

    else if
        (
        Session[base.PageName + ".flagClickGenerateUpdateProc"] != null &&
        (int)Session[base.PageName + ".flagClickGenerateUpdateProc"] == 1)
    {
      GenerateUpdateProcedure(ref dsMeta, ref echo);
      Session[base.PageName + ".flagClickGenerateUpdateProc"] = null;

    } //eof else if

    else if
        (
        Session[base.PageName + ".flagClickGenerateSearchProc"] != null &&
        (int)Session[base.PageName + ".flagClickGenerateSearchProc"] == 1)
    {
      GenerateSearchProcedure(ref dsMeta, ref echo);
      Session[base.PageName + ".flagClickGenerateSearchProc"] = null;

    } //eof else if

    else if
        (
        Session[base.PageName + ".flagClickGenerateDeleteProc"] != null &&
        (int)Session[base.PageName + ".flagClickGenerateDeleteProc"] == 1)
    {
      GenerateDeleteProcedure(ref dsMeta, ref echo);
      Session[base.PageName + ".flagClickGenerateDeleteProc"] = null;

    } //eof else if
    else if (
        Session[base.PageName + ".flagClickUpdateExtendedProps"] != null &&
        (int)Session[base.PageName + ".flagClickUpdateExtendedProps"] == 1)
    {
      GenerateUpdateExtendedProps(ref dsMeta, ref echo);
      Session[base.PageName + ".flagClickGenerateUpdateProc"] = null;

    } //eof else if 
    else if
        (
        Session[base.PageName + ".flagClickGenerateOpenProc"] != null &&
        (int)Session[base.PageName + ".flagClickGenerateOpenProc"] == 1)
    {
      GenerateOpenProcedure(ref dsMeta, ref echo);
      Session[base.PageName + ".flagClickGenerateOpenProc"] = null;

    } //eof else if
    else if (
        Session[base.PageName + ".flagClickAddExtendedProps"] != null &&
        (int)Session[base.PageName + ".flagClickAddExtendedProps"] == 1)
    {
      DbControllers.TableControl tc = new DbControllers.TableControl(connectionStr);

      if (tc.GetTableNameColumnName(ref msg, ref dsMeta, tableName))
      {
        GenerateAddExtendedProps(ref dsMeta, ref echo);
        Session[base.PageName + ".flagClickGenerateUpdateProc"] = null;
      }
      else
      {
        return;
      }

    } //eof else if 



    txtInformer.Rows = (int)Utils.Str.Str.CountLinesInString(echo);
    txtInformer.Text = echo;


  } //eof method 


  private void RecreateDynControlsAfterPostBack()
  {
    holderForDdl = this.HolderForDdl; 
    this.DisplayDropDataBaseSelector( ref holderForDdl);
    this.DisplayDropTableSelector(ref holderForDdl);
    this.DisplayDropProcSelector(ref holderForDdl);
    this.DisplayOutput();
  } //eof method

  #endregion //DisplayMethods



  #region ClickEventHandlers

  protected void ClickDropProcSelector(object sender, EventArgs e)
  {
    string echo = String.Empty; 
    Session["global.msg"] = null; 
    Debugger.WriteIf("CLICK ClickDropProcSelector ");
    DropDownList dropProcSelector = (DropDownList)sender;
    string procName = dropProcSelector.SelectedValue;
    Session[base.PageName + ".procName"] = procName;
    Debugger.WriteIf("CHECK ClickcDropProcSelector --- the selected procName is " + procName);
    Session[base.PageName + ".flagClickDropProcSelector"] = 1;
    Session[base.PageName + ".tableName"] = null;

    this.RecreateDynControlsAfterPostBack();
    
  } //eof method ClickRunSql


  protected void ClickDropTableSelector(object sender, EventArgs e)
  {
    Debugger.WriteIf("CLICK ClickDropTableSelector ");
    DropDownList dropTableSelector = (DropDownList)sender;
    Session [ base.PageName + ".tableName" ] = dropTableSelector.SelectedValue;
    
    Session["global.msg"] = null;
    Debugger.WriteIf("CHECK ClickDropTableSelector --- the selected TableName is " + 
      dropTableSelector.SelectedValue);
    Session[base.PageName + ".flagClickDropTableSelector"] = 1;

    this.RecreateDynControlsAfterPostBack();
  } //eof method 


  protected void ClickDropDataBaseSelector(object sender, EventArgs e)
  {
    Debugger.WriteIf("CLICK ClickDropDataBaseSelector ");
    DropDownList dropDataBaseSelector = (DropDownList)sender;
    string newCurrentDb = dropDataBaseSelector.SelectedValue;
    
    Session["global.msg"] = null;
    string oldConnectionString = (string)Session["global.ConnectionStr"];
    Utils.Debugger.WriteIf("CHECK --- The oldConnectionString is " + oldConnectionString);
    string oldCurrentDb = (string)Session["global.CurrentDb"];
    Utils.Debugger.WriteIf("CHECK --- The oldCurrentDb is " + oldCurrentDb);
    string newConnectionString = oldConnectionString.Replace(oldCurrentDb, newCurrentDb);
    Utils.Debugger.WriteIf("CHECK --- The newConnectionString is " + newConnectionString);
    Session["global.connectionStr"] = newConnectionString;

    Session["global.CurrentDb"] = newCurrentDb;
    Debugger.WriteIf("CHECK ClickDropDataBaseSelector --- the selected DataBaseName is " + newCurrentDb);
    Session[base.PageName + ".flagClickDropDataBaseSelector"] = 1;
    Session[base.PageName + ".procName"] = null;    //start retrieving the procs per database
    Session[base.PageName + ".tableName"] = null; //start retrieving the table lising per database 
    Session[base.PageName + ".flagClickDropProcSelector"] = null; 
    this.RecreateDynControlsAfterPostBack();
  } //eof method 


  protected void ClickRunSql(object sender, EventArgs e)
  {
    Session["global.msg"] = null; 
    string sqlCmd = txtInformer.Text;
    string msg = String.Empty;
    string sqlCommand = txtInformer.Text;
    string connectionStr = Convert.ToString(Session["global.connectionStr"]);
    nsDb.ProcedureControl pc = new
        nsDb.ProcedureControl(connectionStr);
    string procedureName = "RunSql";
    string domainName = (string)Session["global.DomainName"];


    this.RecreateDynControlsAfterPostBack();

    DataSet rds = new DataSet();
    if (pc.RunSql(ref msg, domainName, ref procedureName, ref sqlCmd, ref rds))
    {
      //debug Utils.Debugger.WriteIf("RunSql OK");
      //debug Utils.Debugger.DebugDataSet("from runSql in sql_window", ref rds);
    }
    else
    {
      Utils.Debugger.WriteIf("RunSql NOK");
    }



  } //eof ClickRunSql


  protected void ClickUpdateExtendedProps(object sender, EventArgs e)
  {
    
    Session["global.msg"] = null;
    string tableName = (string)Session[base.PageName + ".tableName"];
    if (tableName == null)
    {
      Session[base.PageName + ".tableName"] = null;
      return;
    }




    Session[base.PageName + ".tableName"] = tableName;
    Session[base.PageName + ".flagClickUpdateExtendedProps"] = 1;

    this.RecreateDynControlsAfterPostBack();
  } //eof method 


  protected void ClickAddExtendedProps(object sender, EventArgs e)
  {
    string tableName = (string)Session[base.PageName + ".tableName"];
    if (tableName == null)
    {
      Session[base.PageName + ".tableName"] = null;
      return;
    }

    Session[base.PageName + ".tableName"] = tableName;
    Session[base.PageName + ".flagClickAddExtendedProps"] = 1;

    this.RecreateDynControlsAfterPostBack();
  } //eof method 


  protected void ClickGenerateUpdateProc(object sender, EventArgs e)
  {
    string tableName = (string)Session[base.PageName + ".tableName"];
    if (tableName == null)
    {
      Session[base.PageName + ".tableName"] = null;
      return;
    }

    Session[base.PageName + ".tableName"] = tableName;
    Session[base.PageName + ".flagClickGenerateUpdateProc"] = 1;

    this.RecreateDynControlsAfterPostBack();
  } //eof method 

  
  protected void ClickGenerateSearchProc(object sender, EventArgs e)
  {
    string tableName = (string)Session[base.PageName + ".tableName"];
    if (tableName == null)
    {
      Session[base.PageName + ".tableName"] = null;
      return;
    }

    Session[base.PageName + ".tableName"] = tableName;
    Session[base.PageName + ".flagClickGenerateSearchProc"] = 1;

    this.RecreateDynControlsAfterPostBack();
  } //eof method 


  protected void ClickGenerateDeleteProc(object sender, EventArgs e)
  {
    string tableName = (string)Session[base.PageName + ".tableName"];
    if (tableName == null)
    {
      Session[base.PageName + ".tableName"] = null;
      return;
    }


    Session[base.PageName + ".tableName"] = tableName;
    Session[base.PageName + ".flagClickGenerateDeleteProc"] = 1;


    this.RecreateDynControlsAfterPostBack();
  } //eof method 


  protected void ClickGenerateOpenProc(object sender, EventArgs e)
  {
    string tableName = (string)Session[base.PageName + ".tableName"];
    if (tableName == null)
    {
      Session[base.PageName + ".tableName"] = null;
      return;
    }


    Session[base.PageName + ".tableName"] = tableName;
    Session[base.PageName + ".flagClickGenerateOpenProc"] = 1;



    this.RecreateDynControlsAfterPostBack();
  } //eof method 


  protected void ClickGenerateInsertProc(object sender, EventArgs e)
  {
    string tableName = (string)Session[base.PageName + ".tableName"];
    if (tableName == null)
    {
      Session[base.PageName + ".tableName"] = null;
      return;
    }

    Session[base.PageName + ".tableName"] = tableName;
    Session[base.PageName + ".flagClickGenerateInsertProc"] = 1;
    this.RecreateDynControlsAfterPostBack();
  } //eof method 


  protected void ClickGenerateInsertValuesProc(object sender, EventArgs e)
  {
    string tableName = (string)Session[base.PageName + ".tableName"];
    if (tableName == null)
    {
      Session[base.PageName + ".tableName"] = null;
      return;
    }


    Session[base.PageName + ".tableName"] = tableName;
    Session[base.PageName + ".flagClickGenerateInsertValuesProc"] = 1;
    this.RecreateDynControlsAfterPostBack();
  } //eof method 


  //TODO: ADD LOGIC TO RETRIEVE TO GRIDVIEW METADATA
  protected void ClickGetMetaData(object sender, EventArgs e)
  {

  } //eof method ClickGetMetaData



  #endregion //ClickEventHandlers


  #region DataMethods





  private void GenerateInsertValuesProcedure(ref DataSet rds, ref string echo)
  {

    string tableName = (string)Session[base.PageName + ".tableName"];
    //BUILD THE USE DB BEGINING 
    string p1 = string.Format(Resources.GV.usePrefix, (string)Session["global.CurrentDb"]);

    this.WriteEcho(ref echo, p1);


    string p0 = "\n SET IDENTITY_INSERT dbo." + tableName + " ON \n GO \n";
    this.WriteEcho(ref echo, p0);


    DataSet ds = new DataSet();
    string connectionStr = Convert.ToString(Session["global.connectionStr"]);
    string domainName = base.DomainName;
    string msg = String.Empty;
    DbControllers.TableControl tco = new DbControllers.TableControl(connectionStr);
    string procedureName = "InsertGenerator";
    string paramMetaValue = "tableName";
    string paramValue = (string)Session[base.PageName + ".tableName"];

    if (tco.RunProcGetDsPassOneParam(ref msg, domainName, procedureName, ref ds, paramValue, paramMetaValue))
    {
      this.Master.ErrorLabel.ForeColor = System.Drawing.ColorTranslator.FromHtml(Resources.GlobalGUISettings.ColorForSuccessMessage);
      Utils.Data.DataSetGastArbeiter.DsToString(ref echo, ref ds);
      string p3 = "SET IDENTITY_INSERT dbo." + tableName + " OFF \n GO \n";
      this.WriteEcho(ref echo, p3);
    }
    else
    {
      this.Master.ErrorLabel.ForeColor = System.Drawing.Color.Red;
    }
    this.Master.ErrorLabel.Text = msg;













  } //eof method 



  private void GenerateInsertProcedure(ref DataSet rds, ref string echo)
  {

    string tableName = (string)Session[base.PageName + ".tableName"];
    string procName = Resources.GV.procedureNamePrefix + "_INSERT_" + tableName;

    
    //BUILD THE USE DB GO BEGINING 
    string p0 = string.Format(Resources.GV.usePrefix, (string)Session["global.CurrentDb"]);
    this.WriteEcho(ref echo, p0);

    string p1 = "IF  EXISTS (SELECT * FROM sys.objects WHERE object_id " + "= OBJECT_ID(N'[dbo]." + procName + "') AND type in (N'P', N'PC')) \n" +
    "DROP PROCEDURE [dbo]." + procName + "\n" + "GO \n\n" +
    "CREATE PROCEDURE [dbo]." + procName + "\n ( \n";

    this.WriteEcho(ref echo, p1);


    #region Loop1
    if (rds != null && rds.Tables[0] != null)
    {
      DataTable dt = rds.Tables[0];
      for (int rowNum = 0; rowNum < dt.Rows.Count; rowNum++)
      {
        string lineEnding = String.Empty;



        DataRow dr = dt.Rows[rowNum];

        //add the description per column with comments for the last row no comma!
        //add also the max length , the table name , if optional
        if (rowNum == dt.Rows.Count - 1)
          lineEnding = " --" + (string)dr["DESCRIPTION"] + ",tb=\"" + tableName +
              "\"" + ",ml=\"" + dr["MAX_LENGTH"] + "\"" + ",op=\"" +
              Convert.ToString(Convert.ToInt16(Convert.ToBoolean((dr["OPTIONAL"])))) +
               "\"" + ",pk=\"" + Convert.ToString(Convert.ToInt16(Convert.ToBoolean((dr["IS_PRIMARY_KEY"])))) + "\"" + " \n ) ";
        else
          lineEnding = " , " + " --" +
              Convert.ToString(dr["DESCRIPTION"]) + ",tb=\"" + tableName + "\"" +
              ",ml=\"" + dr["MAX_LENGTH"] + "\"" + ",op=\"" +
              Convert.ToString(Convert.ToInt16(Convert.ToBoolean(dr["OPTIONAL"]))) +
              "\"" + ",pk=\"" + Convert.ToString(dr["IS_PRIMARY_KEY"]) + "\"" + " \n ";


        //if the table has the mark for the column to be optional added it to proc also
        string isOptional = String.Empty;
        if ((bool)(dr["OPTIONAL"]) == true)
          isOptional = "=null";

        if (!Convert.ToBoolean(dr["IS_PRIMARY_KEY"]))
        {
          //echo = " " + echo + (string)dr["COLUMN_NAME"]; this gives the column names
          this.WriteEcho(ref echo, "@" + (string)dr["COLUMN_NAME"]);

          //specify the length if needed -- @createdBy AS nvarchar(50)
          if (Convert.ToString(dr["DATA_TYPE"]).Equals("nvarchar") ||
                 Convert.ToString(dr["DATA_TYPE"]).Equals("nchar"))
          {
            this.WriteEcho(ref echo, " AS " + (string)dr["DATA_TYPE"] + "(" +
            Convert.ToString(dr["MAX_LENGTH"]) + ")" + isOptional + lineEnding);
          } //eof if
          //no length @gmPercentage AS decimal 
          else
          {
            this.WriteEcho(ref echo, " AS " + (string)dr["DATA_TYPE"] + isOptional + lineEnding);
          }

        } //eof if
        else          //for primary key generate out parameter
        {
          //@Project_Id AS int = null OUT,  
          this.WriteEcho(ref echo, "@" + (string)dr["COLUMN_NAME"] +
              " AS " + (string)dr["DATA_TYPE"] + " OUT " + lineEnding);
        }

      }	//eof foreach (DataTable dt in rds.Tables)
    } //eof if ds !=null 
    else
    {
      this.WriteEcho(ref echo, "NULL DataSet object passed for debugging !!!");
    }

    #endregion Loop1



    string p5 = " \n  AS \n DECLARE @ErrorVar INT \n	BEGIN \n 	" +
        "SET NOCOUNT ON; \n \n INSERT INTO \n [dbo].[" + tableName + "] \n ( \n ";
    this.WriteEcho(ref echo, p5);

    #region Loop2
    if (rds != null && rds.Tables[0] != null)
    {
      DataTable dt = rds.Tables[0];
      for (int rowNum = 0; rowNum < dt.Rows.Count; rowNum++)
      {
        string lineEnding = String.Empty;

        //only the last row has )
        if (rowNum != dt.Rows.Count - 1)
          lineEnding = " , \n ";
        else
          lineEnding = "\n ) \n ";



        DataRow dr = dt.Rows[rowNum];


        if (!Convert.ToBoolean(dr["IS_PRIMARY_KEY"]))
        {
          //echo = " " + echo + (string)dr["COLUMN_NAME"]; this gives the column names
          this.WriteEcho(ref echo, "[" + (string)dr["COLUMN_NAME"] + "]" + lineEnding);

        } //eof if


      }	//eof foreach (DataTable dt in rds.Tables)
    } //eof if ds !=null 
    else
    {
      this.WriteEcho(ref echo, "NULL DataSet object passed for debugging !!!");
    }

    #endregion Loop2



    string p6 = " \n VALUES \n ( \n ";
    this.WriteEcho(ref echo, p6);

    #region Loop3
    if (rds != null && rds.Tables[0] != null)
    {
      DataTable dt = rds.Tables[0];
      for (int rowNum = 0; rowNum < dt.Rows.Count; rowNum++)
      {
        string lineEnding = String.Empty;

        //only the last row has )
        if (rowNum != dt.Rows.Count - 1)
          lineEnding = " , \n ";
        else
          lineEnding = "\n ) \n ";



        DataRow dr = dt.Rows[rowNum];


        if (!Convert.ToBoolean(dr["IS_PRIMARY_KEY"]))
        {
          //echo = " " + echo + (string)dr["COLUMN_NAME"]; this gives the column names
          this.WriteEcho(ref echo, "@" + (string)dr["COLUMN_NAME"] + lineEnding);

        } //eof if

      }	//eof foreach (DataTable dt in rds.Tables)
    } //eof if ds !=null 
    else
    {
      this.WriteEcho(ref echo, "NULL DataSet object passed for debugging !!!");
    }
    #endregion Loop3

    //string p7 = "  \n  END \n";
    //this.WriteEcho(ref echo, p7);

    string p8 = "	SELECT @ErrorVar = @@ERROR \n IF @ErrorVar " +
        "<> 0 \n" + "BEGIN  \n          SELECT \n";
    this.WriteEcho(ref echo, p8);

    string p9, p10, p11, p12, p13, p14 = String.Empty; //those are the parts for the string


    #region Loop4
    if (rds != null && rds.Tables[0] != null)
    {
      p9 = String.Empty;
      p10 = String.Empty;
      p11 = String.Empty;
      p12 = String.Empty;
      p13 = String.Empty;
      p14 = String.Empty;


      DataTable dt = rds.Tables[0];
      for (int rowNum = 0; rowNum < dt.Rows.Count; rowNum++)
      {
        string lineEnding = String.Empty;

        //only the last row has )
        if (rowNum != dt.Rows.Count - 1)
          lineEnding = " , \n ";
        else
          lineEnding = "\n ) \n ";



        DataRow dr = dt.Rows[rowNum];


        if (!Convert.ToBoolean(dr["IS_PRIMARY_KEY"]))
        {
          p13 = p13 + "@" + (string)dr["COLUMN_NAME"] + "=" + "[" + (string)dr["COLUMN_NAME"] + "]" + " AND ";
        } //eof if
        else  //if this is the primary key
        {
          p9 = "@" + (string)dr["COLUMN_NAME"] + "=0 ,";
          //			@Project_Id = project_id --put here all the primary keys with , --p11
          p11 = "@" + (string)dr["COLUMN_NAME"] + ",";
        }



      }	//eof foreach row
      p9 = p9.TrimEnd(new char[] { ',' });
      p11 = p9 = p11.TrimEnd(new char[] { ',' });

      this.WriteEcho(ref echo, p9);
      p10 = " \n RETURN @ErrorVar \n END  \n ELSE \n BEGIN \n SELECT \n";
      this.WriteEcho(ref echo, p10);
      this.WriteEcho(ref echo, p11);
      p12 = " FROM " + tableName + " WHERE \n";
      this.WriteEcho(ref echo, p12);
      p13 = p13.TrimEnd(new char[] { ' ', 'A', 'N', 'D', ' ' });
      this.WriteEcho(ref echo, p13);
      p14 = " \n RETURN 0 \n END \n END \n";
      this.WriteEcho(ref echo, p14);



    } //eof if ds !=null 
    else
    {
      this.WriteEcho(ref echo, "NULL DataSet object passed for debugging !!!");
    }

    #endregion Loop4



  } //eof method 


  private void GenerateUpdateProcedure(ref DataSet rds, ref string echo)
  {

    //BUILD THE USE DB GO BEGINING 
    string p0 = string.Format(Resources.GV.usePrefix, (string)Session["global.CurrentDb"]);
    this.WriteEcho(ref echo, p0);


    string tableName = (string)Session[base.PageName + ".tableName"];
    string procName = Resources.GV.procedureNamePrefix + "_UPDATE_" + tableName;
    this.WriteEcho(ref echo, @"IF  EXISTS (SELECT * FROM sys.objects WHERE object_id " +
    "= OBJECT_ID(N'[dbo]." + procName + "') AND type in (N'P', N'PC')) \n" +
    "DROP PROCEDURE [dbo]." + procName + "\n" +
    "GO \n\n");
    this.WriteEcho(ref echo, "CREATE PROCEDURE [dbo]." + procName + "\n ( \n ");

    #region Loop1
    if (rds != null && rds.Tables[0] != null)
    {
      DataTable dt = rds.Tables[0];
      for (int rowNum = 0; rowNum < dt.Rows.Count; rowNum++)
      {
        DataRow dr = dt.Rows[rowNum];
        string lineEnding = String.Empty;
        if (rowNum == dt.Rows.Count - 1)
          lineEnding = " --" + (string)dr["DESCRIPTION"] + " \n ) ";
        else
          lineEnding = " , " + " --" + (string)dr["DESCRIPTION"] + " \n ";




        //if the table has the mark for the column to be optional added it to proc also
        string isOptional = String.Empty;
        if ((bool)(dr["OPTIONAL"]) == true)
          isOptional = "=null";

        //echo = " " + echo + (string)dr["COLUMN_NAME"]; this gives the column names
        this.WriteEcho(ref echo, "@" + (string)dr["COLUMN_NAME"]);

        //specify the length if needed -- @createdBy AS nvarchar(50)
        if (Convert.ToString(dr["DATA_TYPE"]).Equals("nvarchar") ||
               Convert.ToString(dr["DATA_TYPE"]).Equals("nchar") || Convert.ToString(dr["DATA_TYPE"]).Equals("char"))
        {
          this.WriteEcho(ref echo, " AS " + (string)dr["DATA_TYPE"] + "(" +
          Convert.ToString(dr["MAX_LENGTH"]) + ")" + isOptional + lineEnding);
        } //eof if
        //no length @gmPercentage AS decimal 
        else
        {
          this.WriteEcho(ref echo, " AS " + (string)dr["DATA_TYPE"] + isOptional + lineEnding);
        }



      }	//eof foreach (DataTable dt in rds.Tables)
    } //eof if ds !=null 
    else
    {
      this.WriteEcho(ref echo, "NULL DataSet object passed for debugging !!!");
    }
    #endregion Loop1

    string updateStr = " \n AS \n UPDATE \n [dbo].[" + tableName + "] \n SET \n ";
    this.WriteEcho(ref echo, updateStr);

    #region Loop2
    if (rds != null && rds.Tables[0] != null)
    {
      DataTable dt = rds.Tables[0];
      for (int rowNum = 0; rowNum < dt.Rows.Count; rowNum++)
      {
        string lineEnding = String.Empty;
        DataRow dr = dt.Rows[rowNum];

        //only the last row has )
        if (rowNum != dt.Rows.Count - 1)
          lineEnding = " , \n ";
        else
        {
          lineEnding = "\n WHERE \n " + "[" + (string)dr["COLUMN_NAME"] + "]" +
              "=@" + (string)dr["COLUMN_NAME"];

        }

        if (!Convert.ToBoolean(dr["IS_PRIMARY_KEY"]))
        {
          //echo = " " + echo + (string)dr["COLUMN_NAME"]; this gives the column names
          this.WriteEcho(ref echo, "[" + (string)dr["COLUMN_NAME"] + "]" + "=@"
              + (string)dr["COLUMN_NAME"] + lineEnding);

        } //eof if

      }	//eof foreach (DataTable dt in rds.Tables)
    } //eof if ds !=null 
    else
    {
      this.WriteEcho(ref echo, "NULL DataSet object passed for debugging !!!");
    }
    #endregion Loop2

    string scopeIdentityStr = " \n \n \n SELECT SCOPE_IDENTITY() \n GO \n ";
    WriteEcho(ref echo, scopeIdentityStr);
  } //eof method 


  private void GenerateSearchProcedure(ref DataSet rds, ref string echo)
  {

    //BUILD THE USE DB GO BEGINING 
    string p0 = string.Format(Resources.GV.usePrefix, (string)Session["global.CurrentDb"]);
    this.WriteEcho(ref echo, p0);


    string tableName = (string)Session[base.PageName + ".tableName"];
    string procName = Resources.GV.procedureNamePrefix + "_SEARCH_" + tableName;
    this.WriteEcho(ref echo, @"IF  EXISTS (SELECT * FROM sys.objects WHERE object_id " +
    "= OBJECT_ID(N'[dbo]." + procName + "') AND type in (N'P', N'PC')) \n" +
    "DROP PROCEDURE [dbo]." + procName + "\n" +
    "GO \n\n");
    this.WriteEcho(ref echo, "CREATE PROCEDURE [dbo]." + procName + "\n ( \n ");

    #region Loop1
    if (rds != null && rds.Tables[0] != null)
    {
      DataTable dt = rds.Tables[0];
      for (int rowNum = 0; rowNum < dt.Rows.Count; rowNum++)
      {
        DataRow dr = dt.Rows[rowNum];
        string lineEnding = String.Empty;
        if (rowNum == dt.Rows.Count - 1)
          lineEnding = " --" + (string)dr["DESCRIPTION"] + " \n ) ";
        else
          lineEnding = " , " + " --" + (string)dr["DESCRIPTION"] + " \n ";


        this.WriteEcho(ref echo, "@" + (string)dr["COLUMN_NAME"]);

        //specify the length if needed -- @createdBy AS nvarchar(50)
        if (Convert.ToString(dr["DATA_TYPE"]).Equals("nvarchar") ||
               Convert.ToString(dr["DATA_TYPE"]).Equals("nchar") || Convert.ToString(dr["DATA_TYPE"]).Equals("char"))
        {
          this.WriteEcho(ref echo, " AS " + (string)dr["DATA_TYPE"] + "(" +
          Convert.ToString(dr["MAX_LENGTH"]) + ")" + lineEnding);
        } //eof if
        //no length @gmPercentage AS decimal 
        else
        {
          this.WriteEcho(ref echo, " AS " + (string)dr["DATA_TYPE"] + lineEnding);
        }

      }	//eof foreach (DataTable dt in rds.Tables)
    } //eof if ds !=null 
    else
    {
      this.WriteEcho(ref echo, "NULL DataSet object passed for debugging !!!");
    }
    #endregion Loop1

    string updateStr = " \n AS \n SELECT *  \n [dbo].[" + tableName + "] \n SET \n ";
    this.WriteEcho(ref echo, updateStr);

    #region Loop2
    if (rds != null && rds.Tables[0] != null)
    {
      DataTable dt = rds.Tables[0];
      for (int rowNum = 0; rowNum < dt.Rows.Count; rowNum++)
      {
        string lineEnding = String.Empty;
        DataRow dr = dt.Rows[rowNum];

        //only the last row has )
        if (rowNum != dt.Rows.Count - 1)
          lineEnding = " , \n ";
        else
        {
          lineEnding = "\n WHERE \n " + "[" + (string)dr["COLUMN_NAME"] + "]" +
              "=@" + (string)dr["COLUMN_NAME"];

        }

        if (!Convert.ToBoolean(dr["IS_PRIMARY_KEY"]))
        {
          //echo = " " + echo + (string)dr["COLUMN_NAME"]; this gives the column names
          this.WriteEcho(ref echo, "[" + (string)dr["COLUMN_NAME"] + "]" + "=@"
              + (string)dr["COLUMN_NAME"] + lineEnding);

        } //eof if

      }	//eof foreach (DataTable dt in rds.Tables)
    } //eof if ds !=null 
    else
    {
      this.WriteEcho(ref echo, "NULL DataSet object passed for debugging !!!");
    }
    #endregion Loop2

    string scopeIdentityStr = " \n \n \n SELECT SCOPE_IDENTITY() \n GO \n ";
    WriteEcho(ref echo, scopeIdentityStr);
  } //eof method 


  private void GenerateDeleteProcedure(ref DataSet rds, ref string echo)
  {

    //BUILD THE USE DB GO BEGINING 
    string p0 = string.Format(Resources.GV.usePrefix, (string)Session["global.CurrentDb"]);
    this.WriteEcho(ref echo, p0);


    string tableName = (string)Session[base.PageName + ".tableName"];
    string procName = Resources.GV.procedureNamePrefix + "_DELETE_" + tableName;
    this.WriteEcho(ref echo, @"IF  EXISTS (SELECT * FROM sys.objects WHERE object_id " +
    "= OBJECT_ID(N'[dbo]." + procName + "') AND type in (N'P', N'PC')) \n" +
    "DROP PROCEDURE [dbo]." + procName + "\n" +
    "GO \n\n");
    this.WriteEcho(ref echo, "CREATE PROCEDURE [dbo]." + procName + "\n ( \n ");


    #region Loop1
    if (rds != null && rds.Tables[0] != null)
    {
      DataTable dt = rds.Tables[0];
      for (int rowNum = 0; rowNum < dt.Rows.Count; rowNum++)
      {
        DataRow dr = dt.Rows[rowNum];
        string lineEnding = String.Empty;
        if (rowNum == dt.Rows.Count - 1)
          lineEnding = " --" + (string)dr["DESCRIPTION"] + " \n ) ";
        else
          lineEnding = " , " + " --" + (string)dr["DESCRIPTION"] + " \n ";

        //we need the primary key(s)
        if (Convert.ToBoolean(dr["IS_PRIMARY_KEY"]))
        {

          this.WriteEcho(ref echo, "@" + (string)dr["COLUMN_NAME"]);

          //specify the length if needed -- @createdBy AS nvarchar(50)
          if (Convert.ToString(dr["DATA_TYPE"]).Equals("nvarchar") ||
                 Convert.ToString(dr["DATA_TYPE"]).Equals("nchar") || Convert.ToString(dr["DATA_TYPE"]).Equals("char"))
          {
            this.WriteEcho(ref echo, " AS " + (string)dr["DATA_TYPE"] + "(" +
            Convert.ToString(dr["MAX_LENGTH"]) + ")" + lineEnding);
          } //eof if
          //no length @gmPercentage AS decimal 
          else
          {
            this.WriteEcho(ref echo, " AS " + (string)dr["DATA_TYPE"] + lineEnding);
          }
        } //eof if this is Primary key
      }	//eof foreach (DataTable dt in rds.Tables)
    } //eof if ds !=null 
    else
    {
      this.WriteEcho(ref echo, "NULL DataSet object passed for debugging !!!");
    }
    #endregion Loop1

    string updateStr = " \n AS \n DELETE  [dbo].[" + tableName + "]  \n ";
    this.WriteEcho(ref echo, updateStr);



    string scopeIdentityStr = " \n \n \n SELECT SCOPE_IDENTITY() \n GO \n ";
    WriteEcho(ref echo, scopeIdentityStr);
  } //eof method 


  private void GenerateOpenProcedure(ref DataSet rds, ref string echo)
  {

    //BUILD THE USE DB GO BEGINING 
    string p0 = string.Format(Resources.GV.usePrefix, (string)Session["global.CurrentDb"]);
    this.WriteEcho(ref echo, p0);


    string tableName = (string)Session[base.PageName + ".tableName"];
    string procName = Resources.GV.procedureNamePrefix + "_OPEN_" + tableName;
    this.WriteEcho(ref echo, @"IF  EXISTS (SELECT * FROM sys.objects WHERE object_id " +
    "= OBJECT_ID(N'[dbo]." + procName + "') AND type in (N'P', N'PC')) \n" +
    "DROP PROCEDURE [dbo]." + procName + "\n" +
    "GO \n\n");
    string p1 = "SET ANSI_NULLS ON \n GO \n SET QUOTED_IDENTIFIER ON \n " +
" GO \n ";
    this.WriteEcho(ref echo, p1);
    string p2 = "CREATE PROCEDURE [dbo]." + procName + "\n ( \n";


    string p4 = " \n ) \n AS \n BEGIN \n SET NOCOUNT ON; \n";
    string p7 = " \n SELECT \n";
    string p9 = "\n WHERE ";

    #region Loop1
    if (rds != null && rds.Tables[0] != null)
    {
      DataTable dt = rds.Tables[0];
      Debugger.DebugDataTable("CHECK --- sql_window - open proc", ref dt);
      for (int rowNum = 0; rowNum < dt.Rows.Count; rowNum++)
      {

        DataRow dr = dt.Rows[rowNum];
        string lineEnding = String.Empty;

        //add the description per column with comments for the last row no comma!
        //add also the max length , the table name , if optional
        if (rowNum == dt.Rows.Count - 1)
        {
          lineEnding = " --" + (string)dr["DESCRIPTION"] + ",tb=\"" + tableName +
              "\"" + ",ml=\"" + dr["MAX_LENGTH"] + "\"" + ",op=\"" +
              Convert.ToString(Convert.ToInt16(Convert.ToBoolean((dr["OPTIONAL"])))) +
               "\"" + ",pk=\"" + Convert.ToString(Convert.ToInt16(Convert.ToBoolean((dr["IS_PRIMARY_KEY"])))) + "\"" + " \n  ";
        }
        else
        {
          lineEnding = " , " + " --" +
              Convert.ToString(dr["DESCRIPTION"]) + ",tb=\"" + tableName + "\"" +
              ",ml=\"" + dr["MAX_LENGTH"] + "\"" + ",op=\"" +
              Convert.ToString(Convert.ToInt16(Convert.ToBoolean(dr["OPTIONAL"]))) +
              "\"" + ",pk=\"" + Convert.ToString(dr["IS_PRIMARY_KEY"]) + "\"" + " \n ";

        }


        //only the primary key should not be null -- take all others because of meta
        if (!Convert.ToBoolean(dr["IS_PRIMARY_KEY"]))
        {
          lineEnding = " =NULL " + lineEnding;
        }


        //add the description per column with comments for the last row no comma!
        //add also the max length , the table name , if optional
        p7 = p7 + "\n [" + (string)dr["COLUMN_NAME"] + "] ,";


        if (Convert.ToBoolean(dr["IS_PRIMARY_KEY"]))
        {
          p9 = p9 + " @" + (string)dr["COLUMN_NAME"] + "=" + "[" + (string)dr["COLUMN_NAME"] + "]" + " AND";
        } //eof if



        p2 = p2 + "\n @" + (string)dr["COLUMN_NAME"];

        //specify the length if needed -- @createdBy AS nvarchar(50)
        if (Convert.ToString(dr["DATA_TYPE"]).Equals("nvarchar") ||
               Convert.ToString(dr["DATA_TYPE"]).Equals("nchar"))
        {
          Debugger.WriteIf("CHECK --- primary - nvarchar or nchar ");
          p2 = p2 + " AS " + (string)dr["DATA_TYPE"] + "(" +
          Convert.ToString(dr["MAX_LENGTH"]) + ") " + lineEnding;
        } //eof if
        //no length @gmPercentage AS decimal 
        else
        {
          Debugger.WriteIf("CHECK --- primary all others");
          p2 = p2 + " AS " + (string)dr["DATA_TYPE"] + lineEnding;
        }


      }	//eof foreach (DataTable dt in rds.Tables)

    } //eof if ds !=null 
    else
    {
      this.WriteEcho(ref echo, "NULL DataSet object passed for debugging !!!");
    }
    p2 = p2.TrimEnd(new char[] { ',' });
    p7 = p7.TrimEnd(new char[] { ',' });
    p9 = p9.TrimEnd(new char[] { 'A', 'N', 'D' });
    #endregion Loop1


    this.WriteEcho(ref echo, p2);
    this.WriteEcho(ref echo, p4);
    this.WriteEcho(ref echo, p7);
    string p8 = "\n FROM  [dbo].[" + tableName + "] \n";
    this.WriteEcho(ref echo, p8);
    this.WriteEcho(ref echo, p9);
    string p10 = "\n END \n";
    this.WriteEcho(ref echo, p10);
  } //eof method 


  private void GenerateUpdateExtendedProps(ref DataSet rds, ref string echo)
  {
    //BUILD THE USE DB GO BEGINING 
    string p0 = string.Format(Resources.GV.usePrefix, (string)Session["global.CurrentDb"]);
    this.WriteEcho(ref echo, p0);

    string tableName = (string)Session[base.PageName + ".tableName"];
    string procName = Resources.GV.procedureNamePrefix + "_UPDATE_EXTPROPS_" + tableName;

    #region Loop1
    if (rds != null && rds.Tables[0] != null)
    {
      DataTable dt = rds.Tables[0];
      for (int rowNum = 0; rowNum < dt.Rows.Count; rowNum++)
      {
        DataRow dr = dt.Rows[rowNum];
        echo = echo + "EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=N'";
        echo = echo + (string)dr["DESCRIPTION"];
        echo = echo + "' , @level0type=N'SCHEMA',@level0name=N'dbo', ";
        echo = echo + "@level1type=N'TABLE',@level1name=N'" + tableName;
        echo = echo + "', @level2type=N'COLUMN',@level2name=N'";
        echo = echo + (string)dr["COLUMN_NAME"] + "'\n  \n";


      }	//eof foreach (DataTable dt in rds.Tables)
    } //eof if ds !=null 
    else
    {
      this.WriteEcho(ref echo, "NULL DataSet object passed for debugging !!!");
    }
    #endregion Loop1
  } //eof method 


  private void GenerateAddExtendedProps(ref DataSet rds, ref string echo)
  {

    //BUILD THE USE DB GO BEGINING 
    string p0 = string.Format(Resources.GV.usePrefix, (string)Session["global.CurrentDb"]);
    this.WriteEcho(ref echo, p0);

    string tableName = (string)Session[base.PageName + ".tableName"];
    string procName = Resources.GV.procedureNamePrefix + "_CREATE_EXTPROPS_" + tableName;

    #region Loop1
    if (rds != null && rds.Tables[0] != null)
    {
      DataTable dt = rds.Tables[0];
      for (int rowNum = 0; rowNum < dt.Rows.Count; rowNum++)
      {

        DataRow dr = dt.Rows[rowNum];
        string desc = "title=\"" + (string)dr["COLUMN_NAME"] + "\",visible=\"1\"";
        echo = echo + "EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'";
        echo = echo + desc;
        echo = echo + "' , @level0type=N'SCHEMA',@level0name=N'dbo', ";
        echo = echo + "@level1type=N'TABLE',@level1name=N'" + tableName;
        echo = echo + "', @level2type=N'COLUMN',@level2name=N'";
        echo = echo + (string)dr["COLUMN_NAME"] + "'\n  \n";
      }	//eof foreach (DataTable dt in rds.Tables)
    } //eof if ds !=null 
    else
    {
      this.WriteEcho(ref echo, "NULL DataSet object passed for debugging !!!");
    }
    #endregion Loop1
  } //eof method 


  private void WriteEcho(ref string echo, string toAdd)
  {
    echo = echo + toAdd;
  } //WriteEcho 


  #endregion DataMethods


  #region UtilityMethods


  #endregion UtilityMethods
} //eof class _Default 
